﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// 文件名：ClsStockOrdersAffirmDAL.cs
/// 作  者：SAM
/// 时  间：2006.12.30
/// 说  明：ClsStockOrdersAffirmDAL类用于库存管理信息相关处理
/// </summary>
public class ClsStockOrdersAffirmDAL
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    /// <summary>
    /// 根据盘点单号获取所有库存盘点从表信息
    /// <param name="Tm_On">盘点单号</param>
    /// </summary>
    /// <returns>返回库存盘点从表信息数据集</returns>
    public DataSet GetStockCheckChilds(string Tm_No)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetStockOrdersAffirmD", con);//

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@tm_no", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@tm_no"].Value = Tm_No;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;       
    }

    /// <summary>
    /// 实盘确认单确认
    /// </summary>
    /// <returns></returns>
    public int UpdateStockCheckState(string Tm_No, string ss_id, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditStockOrdersAffirmInto", con);
        SqlParameter PrmOutPut = new SqlParameter();

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@tm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@tm_no"].Value = Tm_No;
        com.Parameters.Add("@s_id", SqlDbType.NVarChar, 30);
        com.Parameters["@s_id"].Value = ss_id;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;
        PrmOutPut = com.Parameters.Add("@errcount", SqlDbType.Int, 4);
        PrmOutPut.Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return Int32.Parse(com.Parameters["@errcount"].Value.ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 增加实盘单确认单信息 BY SAM 2006-12-30
    /// </summary>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    public Boolean AddCheckStock(string s_id, DateTime s_date, string sTm_No, DateTime tm_date, string sWhi_id, string sOper, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddStockOrdersAffirmM", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@s_id", SqlDbType.NVarChar, 30);
        com.Parameters["@s_id"].Value = s_id;
        com.Parameters.Add("@s_date", SqlDbType.DateTime, 8);
        com.Parameters["@s_date"].Value = s_date;

        com.Parameters.Add("@tm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@tm_no"].Value = sTm_No;
        com.Parameters.Add("@tm_date", SqlDbType.DateTime, 8);
        com.Parameters["@tm_date"].Value = tm_date;
        com.Parameters.Add("@Whi_id", SqlDbType.NVarChar, 30);
        com.Parameters["@Whi_id"].Value = sWhi_id;
        com.Parameters.Add("@Oper", SqlDbType.NVarChar, 30);
        com.Parameters["@Oper"].Value = sOper;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 增加实盘单确认明细信息 BY SAM 2006-12-30
    /// </summary>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    public Boolean AddChildCheckStock(string ss_id, string sIm_no, string sLot_no, string soo_no, decimal dQty, decimal dQty1, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddStockOrdersAffirmD", con);
        com.CommandType = CommandType.StoredProcedure;



        com.Parameters.Add("@s_id", SqlDbType.NVarChar, 30);//实盘确认单号
        com.Parameters["@s_id"].Value = ss_id;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);//商品编号
        com.Parameters["@im_no"].Value = sIm_no;
        com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 30);//批号
        com.Parameters["@lot_no"].Value = sLot_no;
        com.Parameters.Add("@oo_no", SqlDbType.NVarChar, 30);//识别码
        com.Parameters["@oo_no"].Value = soo_no;
        com.Parameters.Add("@qty", SqlDbType.Decimal,28);//
        com.Parameters["@qty"].Value = dQty;
        com.Parameters.Add("@qty1", SqlDbType.Decimal,28);//
        com.Parameters["@qty1"].Value = dQty1;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改实盘单信息 BY SAM 修改 2006-12-30
    /// </summary>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    public Boolean UpdateCheckStock(string s_id, DateTime date, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateStockOrdersAffirmM", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@s_id", SqlDbType.NVarChar, 30);
        com.Parameters["@s_id"].Value = s_id;
        com.Parameters.Add("@date", SqlDbType.DateTime, 8);
        com.Parameters["@date"].Value = date;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改实盘确认单明细信息 修改BY SAM 2006-12-30
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    public Boolean UpDateChildCheckStock(string ss_id, string sIm_no, string sLot_no, decimal dQty, decimal dQty1, string sRemark, string strOldBatchNo)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateStockOrdersAffirmD", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@s_id", SqlDbType.NVarChar, 30);
        com.Parameters["@s_id"].Value = ss_id;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = sIm_no;
        com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 30);
        com.Parameters["@lot_no"].Value = sLot_no;
        com.Parameters.Add("@qty", SqlDbType.Decimal, 28);
        com.Parameters["@qty"].Value = dQty;
        com.Parameters.Add("@qty1", SqlDbType.Decimal, 28);
        com.Parameters["@qty1"].Value = dQty1;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        com.Parameters.Add("@strOldBatchNo", SqlDbType.NVarChar, 255);
        com.Parameters["@strOldBatchNo"].Value = strOldBatchNo;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废实盘单信息
    /// </summary>
    /// <param name="sTm_type"></param>
    /// <param name="sTm_no"></param>
    /// <param name="iIs_Visible">作废状态1为临时作废，－1为彻底作废</param>
    /// <returns></returns>
    public Boolean DeleteCheckStock(string s_id, int iIs_Visible)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteStockOrdersAffirmM", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@s_id", SqlDbType.NVarChar, 20);
        com.Parameters["@s_id"].Value = s_id;
        com.Parameters.Add("@Is_Visible", SqlDbType.Int, 1);
        com.Parameters["@Is_Visible"].Value = iIs_Visible;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废实盘单明细信息
    /// </summary>
    /// <param name="Is_Visible">作废状态－1为临时作废，－2为彻底作废</param>
    /// <returns>返回作废是否成功</returns>
    public Boolean DeleteChildCheckStock(string ss_id, string sIm_no, string sLot_no, int iIs_Visible)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteChildStockOrdersAffirmD", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@s_id", SqlDbType.NVarChar, 30);
        com.Parameters["@s_id"].Value = ss_id;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = sIm_no;
        com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 30);
        com.Parameters["@lot_no"].Value = sLot_no;
        com.Parameters.Add("@Is_Visible", SqlDbType.Int, 1);
        com.Parameters["@Is_Visible"].Value = iIs_Visible;


        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    ///  实盘单确认 
    /// <returns>CREATE BY KIDY ON 20061214</returns>
    /// </summary>
    public String CheckWhetherAudtit(string sTm_No, string sTm_Type, int iState, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditCheckStockChangeStore", con);
        com.CommandType = CommandType.StoredProcedure;
        SqlParameter ParOutPut = new SqlParameter();

        com.Parameters.Add("@s_id", SqlDbType.NVarChar, 30);
        com.Parameters["@s_id"].Value = sTm_No;
        com.Parameters.Add("@dm_state", SqlDbType.Int, 4);
        com.Parameters["@dm_state"].Value = iState;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;
        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sTm_Type;
        ParOutPut = com.Parameters.Add("@relot_no", SqlDbType.NVarChar, 30);
        ParOutPut.Direction = ParameterDirection.Output;
        ParOutPut = com.Parameters.Add("@returnState", SqlDbType.NVarChar, 2);
        ParOutPut.Direction = ParameterDirection.Output;
        try
        {
            con.Open();
            com.ExecuteNonQuery();
            string strState = com.Parameters["@returnState"].Value.ToString();
            string strReLot_no = com.Parameters["@relot_no"].Value.ToString();
            if (strState == "N")
            {
                return "N" + strReLot_no;
            }
            else if (strState == "Q")
            {
                return "Q" + strReLot_no;
            }
            else
            {
                return "0";
            }

            return strReLot_no;
        }
        catch
        {
            return "";
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 获得可以修改的最大值
    /// </summary>
    /// <param name="ss_id"></param>
    /// <param name="sIm_no"></param>
    /// <param name="sLot_no"></param>
    /// <returns></returns>
    public decimal GetStockOrdersMaxQty(string ss_id, string sIm_no,string sLot_no)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_GetStockOrdersMaxQty", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@s_id", SqlDbType.NVarChar, 30);
        com.Parameters["@s_id"].Value = ss_id;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = sIm_no;
        com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 30);
        com.Parameters["@lot_no"].Value = sLot_no;


        try
        {
            con.Open();
            return decimal.Parse(com.ExecuteScalar().ToString());

        }
        catch
        {
            return -1;//这个地方会引起问题
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 根据查询状态获取盘点单需要的主表信息
    /// </summary>CREATE BY SAM ON 2007-01-03
    /// <returns>返回主表信息数据集</returns>
    public DataSet GetOrderInformationByStatu(string UserID, int iQueryType)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetStatutboStockOrdersAffirmM", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;

        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;
        dat.SelectCommand.Parameters.Add("@queryType", SqlDbType.Int, 2);
        dat.SelectCommand.Parameters["@queryType"].Value = iQueryType;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 取消确认实盘确认单
    /// </summary>
    /// <returns></returns>
    public Boolean OAuditStockOrdersAffirmInto(string tm_no, string s_id)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_OAuditStockOrdersAffirmInto", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@tm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@tm_no"].Value = tm_no;
        com.Parameters.Add("@s_id", SqlDbType.NVarChar, 30);
        com.Parameters["@s_id"].Value = s_id;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断改变库存后  实盘单  是否可以取消确认CREATE BY KIDY ON 20070130
    /// <returns>返回是否可以取消确认</returns>
    public bool WhetherNonAudit(string sBodNo)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_WhetherNonAuditByCheck", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@BodNo", SqlDbType.NVarChar, 30);
        com.Parameters["@BodNo"].Value = sBodNo;

        try
        {
            con.Open();
            Boolean bAuditState = (Boolean)com.ExecuteScalar();
            return bAuditState;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

}
